package com.yhp.dao.impl;

import com.yhp.bean.Users;
import com.yhp.dao.UsersDao;
import com.yhp.util.DBUtilsPlus;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsersDaoImpl  extends DBUtilsPlus implements UsersDao {


    @Override
    public int insert(Users users) {
        int i=0;
        try {
            String sql=
                    "insert into users(username,age,sex,password,status) values(?,?,?,?,0)";
            List params=new ArrayList();
            params.add(users.getUserName());
            params.add(users.getAge());
            params.add(users.getSex());
            params.add(users.getPassWord());
            i = updateDB(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    @Override
    public boolean checkuser(String name) {
        try {
            String sql="select * from users where username=?";
            List params=new ArrayList();
            params.add(name);
            resultSet=selectDB(sql,params);
            if(resultSet.next()){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return false;
    }

    @Override
    public int updateuser(Users users) {
        int i=0;
        try {
            String sql="update users set username=?,age=?,sex=?,password=? where userid=?";
            List params=new ArrayList();
            params.add(users.getUserName());
            params.add(users.getAge());
            params.add(users.getSex());
            params.add(users.getPassWord());
            params.add(users.getUserId());
            i = updateDB(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    @Override
    public Users selectByid(int userid) {
        Users users = new Users();
        try {
            String sql="select * from users where userid=?";
            List params=new ArrayList();
            params.add(userid);
            resultSet=selectDB(sql,params);
            while(resultSet.next()){
                users.setUserId(resultSet.getInt("userid"));
                users.setUserName(resultSet.getString("username"));
                users.setPassWord(resultSet.getString("password"));
                users.setSex(resultSet.getString("sex"));
                users.setAge(resultSet.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return users;
    }

    @Override
    public int deleteById(int userid) {
        int i =0;
        try {
            String sql="update users set status=1 where userid=?";
            List params=new ArrayList();
            params.add(userid);
            i = updateDB(sql, params);//i 表示受影响的行数
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    @Override
    public int deleteUser(int userid) {
        int i =0;
        try {
            String sql="delete from users where userid=?";
            List params=new ArrayList();
            params.add(userid);
            i = updateDB(sql, params);//i 表示受影响的行数
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    @Override
    public int totalCount(String uname,String usex) {
        int count=0;
        try {
            //String sql="select count(*) from users where status=0";
            StringBuffer sql=new StringBuffer(" select count(*) from users where status=0 ");
            List params=new ArrayList();
            if(uname!=null&&uname.trim().length()>0){
                sql.append(" and username like ? ");
                params.add("%"+uname+"%");
            }
            if(usex!=null && usex.length()>0 &&!"noselect".equals(usex)){ //只有男或女的值时才进来
                sql.append(" and  sex=? ");
                params.add(usex);
            }
            resultSet=selectDB(sql.toString(),params);
            while(resultSet.next()){
                //int count=resultSet.getInt("count(*)");
                count=resultSet.getInt(1);//列的位置从1开始数
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return count;
    }

    @Override
    public List<Users> selectAll(int pageIndex,int size,String uname,String usex) {
        List usersList=new ArrayList();
        try {
           // String sql="select * from users  where status=0 and uname like ? or usex=?  limit ?,?";
            //注意:字符串两侧要加空格
            StringBuffer sql=new StringBuffer(" select * from users  where status=0 ");
            List params=new ArrayList();
            //当uname="" usex="" 时，两个if都不会进
            if(uname!=null&&uname.trim().length()>0){
                sql.append(" and username like ? ");
                params.add("%"+uname+"%");
            }
            if(usex!=null && usex.length()>0&& !"noselect".equals(usex)){ //只有男或女的值时才进来
                sql.append(" and  sex=? ");
                params.add(usex);
            }
            sql.append("  limit ?,? ");
            params.add((pageIndex-1)*size);
            params.add(size);

            resultSet= selectDB(sql.toString(),params);
            while(resultSet.next()){
                Users u1=new Users();
                u1.setUserId(resultSet.getInt("userid"));
                u1.setAge(resultSet.getInt("age"));
                u1.setPassWord(resultSet.getString("password"));
                u1.setSex(resultSet.getString("sex"));
                u1.setUserName(resultSet.getString("username"));
                usersList.add(u1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return usersList;
    }

    @Override
    public Users login(String username, String pass) {
        Users u1 = null;
        try {
            String sql="select * from users where username=? and password=? and status=0";
            List params=new ArrayList();
            params.add(username);
            params.add(pass);
            resultSet=selectDB(sql,params);
            //将结果集的内容拿到java对象中（当结果集有数据时，就会进入得到循环中，每一次循环都会拿到数据库的中一行数据）
            while(resultSet.next()){
                u1=new Users();
                u1.setUserId(resultSet.getInt("userid"));
                u1.setAge(resultSet.getInt("age"));
                u1.setPassWord(resultSet.getString("password"));
                u1.setSex(resultSet.getString("sex"));
                u1.setUserName(resultSet.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return u1;
    }
}
